package com.starkuz.dao.impl.sites;

import com.starkuz.dao.ifaces.sites.OrderSiteDao;
import com.starkuz.model.SiteStatuses;
import com.starkuz.model.sites.OrderSite;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.sql.Date;
import java.util.*;

@Repository
public class OrderSiteDaoImpl implements OrderSiteDao {

    private static final String SELECT_ALL_ORDER_SITES =
            "select * from order_sites";
    private static final String SELECT_ALL_ACTUAL_ORDER_SITES_BY_USER_ID =
            "select * from order_sites where ownerId = ? and not status = 'deleted'";
    private static final String SELECT_ALL_ORDER_SITES_BY_USER_ID =
            "select * from order_sites where ownerId = ?";
    private static final String SELECT_ORDER_SITE_BY_ID = "select * from order_sites where id = ?";
    private static final String SELECT_ALL_ORDER_SITES_BY_USER_ID_AND_STATUS =
            "select * from order_sites where ownerId = ? and status = ?";
    private static final String SELECT_ALL_ORDER_SITES_BY_STATUS =
            "select * from order_sites where status = ?";
    private static final String SELECT_ALL_ORDER_SITES_BY_USER_ID_AND_STATUS_AND_DATE_BETWEEN =
            "select * from order_sites where ownerId = ? and status = ? and dateMade between ? and ?";

    private static final String DELETE_ORDER_SITE_BY_ID = "delete from order_sites where id = ?";
    private static final String UPDATE_ORDER_SITE_TO_DELETED =
            "update order_sites set status = 'deleted' where id = ?";
    private static final String INSERT_ORDER_SITE =
            "insert into order_sites (ownerId, idTemplate, titleTag, keywordTag, descripTag, mainHeader, briefDef," +
                    " companyName, mySite, dopText, marketType," +
                    " status,bgImg, blackBox, btnTxt, dateMade, dateLastChange)" +
                    " values (?, ?, ?, ?, ?, ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?)";
    private static final String SET_ORDER_SITE_STATUS_BY_ID = "update order_sites set status = ? where id = ?";
    private static final String UPDATE_ORDER_SITE =
            "update order_sites set ownerId = ?, idTemplate=?, titleTag = ?, keywordTag = ?, descripTag = ?," +
                    "mainHeader = ?, briefDef = ?, companyName = ?, mySite = ?," +
                    "dopText = ?, marketType = ?," +
                    "bgImg = ?, blackBox = ?, btnTxt = ? where id = ?;";

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public List<OrderSite> getAllOrderSites() {
        List<OrderSite> sites = new ArrayList<OrderSite>();
        Connection con = null;
        ResultSet res = null;
        try {
            con = ds.getConnection();
            Statement st = con.createStatement();
            res = st.executeQuery(SELECT_ALL_ORDER_SITES);
            sites = resultSetToOrderSitesList(res, sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                res.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public OrderSite findOrderSiteById(Integer id) {
        OrderSite orderSite = new OrderSite();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ORDER_SITE_BY_ID);
            psql.setInt(1, id);
            orderSite = resultSetToOrderSite(psql.executeQuery(), orderSite);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return orderSite;
    }

    @Override
    public List<OrderSite> getAllOrderSitesForUser(Integer userId) {
        List<OrderSite> sites = new ArrayList<OrderSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ORDER_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToOrderSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<OrderSite> getAllActualOrderSitesForUser(Integer userId) {
        List<OrderSite> sites = new ArrayList<OrderSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ACTUAL_ORDER_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToOrderSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<OrderSite> getAllOrderSitesForUserByStatus(Integer userId, String status) {
        List<OrderSite> sites = new ArrayList<OrderSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ORDER_SITES_BY_USER_ID_AND_STATUS);
            psql.setInt(1, userId);
            psql.setString(2, status);
            sites = resultSetToOrderSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<OrderSite> getAllSitesByStatus(String status) {
        List<OrderSite> sites = new ArrayList<OrderSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ORDER_SITES_BY_STATUS);
            psql.setString(1, status);
            sites = resultSetToOrderSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public void setStatusBySiteId(Integer siteId, String status) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SET_ORDER_SITE_STATUS_BY_ID);
            psql.setString(1, status);
            psql.setInt(2, siteId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    @Override
    public void addOrderSite(OrderSite orderSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(INSERT_ORDER_SITE);
            psql.setInt(1, orderSite.getOwnerId());
            psql.setInt(2, orderSite.getIdTemplate());
            psql.setString(3, orderSite.getTitleTag());
            psql.setString(4, orderSite.getKeywordTag());
            psql.setString(5, orderSite.getDescripTag());
            psql.setString(6, orderSite.getMainHeader());
            psql.setString(7, orderSite.getBriefDef());
            psql.setString(8, orderSite.getCompanyName());
            psql.setString(9, orderSite.getMySite());
            psql.setString(10, orderSite.getDopText());
            psql.setString(11, orderSite.getMarketType());
            psql.setString(12, SiteStatuses.SITE_STATUS_ACTIVE); //при создании сайт активен
            psql.setString(13, orderSite.getBgImg());
            psql.setInt(14, orderSite.getBlackBox());
            psql.setString(15, orderSite.getBtnTxt());
            psql.setDate(16, new Date(new java.util.Date().getTime())); //дата в момент создания сайта
            psql.setDate(17, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteOrderSiteById(Integer orderSiteId) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con  = ds.getConnection();
            psql = con.prepareStatement(UPDATE_ORDER_SITE_TO_DELETED);
            psql.setInt(1, orderSiteId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void updateOrderSite(OrderSite orderSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(UPDATE_ORDER_SITE);
            psql.setInt(1, orderSite.getOwnerId());
            psql.setInt(2, orderSite.getIdTemplate());
            psql.setString(3, orderSite.getTitleTag());
            psql.setString(4, orderSite.getKeywordTag());
            psql.setString(5, orderSite.getDescripTag());
            psql.setString(6, orderSite.getMainHeader());
            psql.setString(7, orderSite.getBriefDef());
            psql.setString(8, orderSite.getCompanyName());
            psql.setString(9, orderSite.getMySite());
            psql.setString(10, orderSite.getDopText());
            psql.setString(11, orderSite.getMarketType());
            psql.setString(12, orderSite.getBgImg());
            psql.setInt(13, orderSite.getBlackBox());
            psql.setString(14, orderSite.getBtnTxt());
            psql.setInt(15, orderSite.getId());
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private OrderSite resultSetToOrderSite(ResultSet rs, OrderSite orderSite) throws SQLException {
        while (rs.next()) {
            orderSite = new OrderSite();
            orderSite.setId(rs.getInt("id"));
            orderSite.setIdTemplate(rs.getInt("idTemplate"));
            orderSite.setOwnerId(rs.getInt("ownerId"));
            orderSite.setTitleTag(rs.getString("titleTag"));
            orderSite.setKeywordTag(rs.getString("keywordTag"));
            orderSite.setDescripTag(rs.getString("descripTag"));
            orderSite.setMainHeader(rs.getString("mainHeader"));
            orderSite.setBriefDef(rs.getString("briefDef"));
            orderSite.setCompanyName(rs.getString("companyName"));
            orderSite.setMySite(rs.getString("mySite"));
            orderSite.setDopText(rs.getString("dopText"));
            orderSite.setMarketType(rs.getString("marketType"));
            orderSite.setStatus(rs.getString("status"));
            orderSite.setBgImg(rs.getString("bgImg"));
            orderSite.setBlackBox(rs.getInt("blackBox"));
            orderSite.setDateMade(rs.getDate("dateMade"));
            orderSite.setDateLastChange(rs.getDate("dateLastChange"));
            orderSite.setBtnTxt(rs.getString("btnTxt"));
        }
        return orderSite;
    }

    private List<OrderSite> resultSetToOrderSitesList(ResultSet rs, List<OrderSite> sites) throws SQLException {
        while (rs.next()) {
            OrderSite orderSite = new OrderSite();
            orderSite.setId(rs.getInt("id"));
            orderSite.setOwnerId(rs.getInt("ownerId"));
            orderSite.setIdTemplate(rs.getInt("idTemplate"));
            orderSite.setTitleTag(rs.getString("titleTag"));
            orderSite.setKeywordTag(rs.getString("keywordTag"));
            orderSite.setDescripTag(rs.getString("descripTag"));
            orderSite.setMainHeader(rs.getString("mainHeader"));
            orderSite.setBriefDef(rs.getString("briefDef"));
            orderSite.setCompanyName(rs.getString("companyName"));
            orderSite.setMySite(rs.getString("mySite"));
            orderSite.setDopText(rs.getString("dopText"));
            orderSite.setMarketType(rs.getString("marketType"));
            orderSite.setStatus(rs.getString("status"));
            orderSite.setBgImg(rs.getString("bgImg"));
            orderSite.setBlackBox(rs.getInt("blackBox"));
            orderSite.setDateMade(rs.getDate("dateMade"));
            orderSite.setDateLastChange(rs.getDate("dateLastChange"));
            orderSite.setBtnTxt(rs.getString("btnTxt"));
            sites.add(orderSite);
        }
        return sites;
    }
}
